using System;
using System.Data;
using System.Data.SqlClient;
using SistemaLib.sipe.db.general;
using SistemaLib.sipe.lib;

namespace SistemaLib.sipe.db.sqlserver
{
	/// <summary>
	/// Summary description for DbConnection.
	/// </summary>
	public class DbConnection : sipe.db.general.IDbConnectionBase
	{
		#region Campos
		
		private SqlConnection l_conn;
		private SqlTransaction l_trans;
		private string l_cadenaConexion;
		private BusinessException l_errores;
        private bool _noCerrar = false;

		#endregion

		#region propiedades

		private SqlConnection connection
		{
			get
			{
				return l_conn;
			}
			set
			{
				l_conn = value;
			}
		}

		private SqlTransaction transaction
		{
			get
			{
				return l_trans;
			}
			set
			{
				l_trans = value;
			}
		}

		public bool isTransaction
		{
			get
			{
				if ( this.transaction == null )
				{
					return false;
				}
				else
				{
					return true;
				}
			}
		}

		public BusinessException Errores
		{
			get
			{
				return l_errores;
			}
			set
			{
				l_errores = value;
			}
		}

        public bool NoCerrar
        {
            get { return _noCerrar; }
            set { _noCerrar = value; }
        }

        public bool IsOpen
        {
            get 
            {
                if (this.connection == null)
                    return false;

                if (this.connection.State != ConnectionState.Open)
                    return false;

                return true;
            }
        }


		#endregion

		public DbConnection()
		{
			l_cadenaConexion = sipe.consts.Configuracion.GetConnectionString();
			l_errores = new BusinessException();
		}

		#region Metodos

        public void Open()
        {
            if (l_cadenaConexion == string.Empty || l_cadenaConexion == null)
                throw new ArgumentNullException("The connection string has not been set yet.");

            if (this.connection == null)
                this.connection = new SqlConnection(l_cadenaConexion);

            if (this.connection.State != ConnectionState.Open)
                this.connection.Open();
        }

		public void Open( bool noCerrar)
		{
            this.NoCerrar = noCerrar;
            Open();
		}

		public void Close()
		{
			if ( ! this.isTransaction && ! this.NoCerrar)
				this.connection.Close();
		}

		public void Dispose()
		{
			Close();
			this.connection = null;
			l_cadenaConexion = null;
		}

		public void BeginTransaction()
		{
			if ( this.isTransaction )
			{
				throw new Exception( "There is a current transaction.");
			}
			this.transaction = this.connection.BeginTransaction();
		}

		public void CommitTransaction()
		{
			if ( ! this.isTransaction )
			{
				throw new Exception( "There is not a current transaction.");
			}
			this.transaction.Commit();
			this.transaction = null;
		}

		public void RollbackTransaction()
		{
			if ( ! this.isTransaction )
			{
				throw new Exception( "There is not a current transaction.");
			}
			try
			{
				this.transaction.Rollback();
			} 
			catch ( SqlException e)
			{
				if ( e.Number != 3903 )
				{
					throw e;
				}
			}
			this.transaction = null;
		}

		

		
		public bool RunDataSetProcedure(string procedure,out DataSet dataSet)
		{
			IDbCommand command = this.CreateCommand(procedure,CommandType.Text);

			return RunDataSetProcedure(command,out dataSet);
		}

		public bool RunDataSetProcedure(string procedure,CommandType commandType,out DataSet dataSet)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType);

			return RunDataSetProcedure(command,out dataSet);
		}

		public bool RunDataSetProcedure(string procedure,DbParameterCollection parameters,out DataSet dataSet)
		{
			IDbCommand command = this.CreateCommand(procedure,CommandType.StoredProcedure,parameters,DbTimeouts.Corto);

			return RunDataSetProcedure(command,out dataSet);
		}

		public bool RunDataSetProcedure(string procedure,DbParameterCollection parameters, CommandType commandType,out DataSet dataSet)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType,parameters,DbTimeouts.Corto);

			return RunDataSetProcedure(command,out dataSet);
		}

        public bool RunDataSetProcedureLarguisimo(string procedure, DbParameterCollection parameters, CommandType commandType, out DataSet dataSet)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType,parameters,DbTimeouts.Larguisimo);

			return RunDataSetProcedure(command,out dataSet);
		}

        public bool RunDataSetProcedure(string procedure, DbParameterCollection parameters, CommandType commandType, out DataSet dataSet, DbTimeouts timeouts)
        {
            IDbCommand command = this.CreateCommand(procedure, commandType, parameters, timeouts);

            return RunDataSetProcedure(command, out dataSet);
        }

		public bool RunNonQueryProcedure(string procedure,CommandType commandType,out int recordsAffected)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType);

			return RunNonQueryProcedure(command,out recordsAffected);
		}
				
		public bool RunNonQueryProcedure(string procedure,DbParameterCollection parameters,out int recordsAffected)
		{
			IDbCommand command = this.CreateCommand(procedure,CommandType.StoredProcedure,parameters,DbTimeouts.Corto);

			return RunNonQueryProcedure(command,out recordsAffected);
		}

		public bool RunNonQueryProcedure(string procedure,DbParameterCollection parameters,CommandType commandType,out int recordsAffected)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType,parameters,DbTimeouts.Corto);

			return RunNonQueryProcedure(command,out recordsAffected);
		}

		public bool RunNonQueryProcedure(string procedure,DbParameterCollection parameters,CommandType commandType,out int recordsAffected, DbTimeouts timeout)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType,parameters,timeout);

			return RunNonQueryProcedure(command,out recordsAffected);
		}

		public bool RunNonQueryProcedure(string procedure,out int recordsAffected)
		{
			IDbCommand command = this.CreateCommand(procedure);
			
			return RunNonQueryProcedure(command,out recordsAffected);
		}

		public bool RunScalarProcedure(string procedure,out object value)
		{
			IDbCommand command = this.CreateCommand(procedure);

			return RunScalarProcedure(command,out value);
		}

		public bool RunScalarProcedure(string procedure,CommandType commandType,out object value)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType);

			return RunScalarProcedure(command,out value);
		}

		public bool RunScalarProcedure(string procedure,DbParameterCollection parameters,out object value)
		{
			IDbCommand command = this.CreateCommand(procedure,CommandType.StoredProcedure,parameters,DbTimeouts.Corto);

			return RunScalarProcedure(command,out value);
		}

		public bool RunScalarProcedure(string procedure,DbParameterCollection parameters,CommandType commandType,out object value)
		{
			IDbCommand command = this.CreateCommand(procedure,commandType,parameters,DbTimeouts.Corto);

			return RunScalarProcedure(command,out value);
		}

		private IDbCommand CreateCommand(string procedure)
		{
			return CreateCommand(procedure,CommandType.Text,null,DbTimeouts.Corto);
		}

		private IDbCommand CreateCommand(string procedure,CommandType commandType) 
		{
			return CreateCommand(procedure,commandType,null,DbTimeouts.Corto);
		}

		private IDbCommand CreateCommand(string procedure, CommandType commandType, DbParameterCollection parameters, DbTimeouts timeout) 
		{
			Open();

			IDbCommand command = new SqlCommand(procedure,connection);
			command.CommandType = commandType;

			if ( this.isTransaction )
			{
				command.Transaction = this.transaction;
			}

			switch ( timeout )
			{
				case DbTimeouts.Larguisimo:
					command.CommandTimeout = 7200;  // 2hs min
					break;
				case DbTimeouts.Largo:
					command.CommandTimeout = 1200;  // 20 min
					break;
				case DbTimeouts.Medio:
					command.CommandTimeout = 300;  // 5 min
					break;
				default:
					command.CommandTimeout = 60;  // 1 min
					break;
			}

			if (parameters != null) 
			{
				foreach (IDbDataParameter parameter in parameters)
					command.Parameters.Add(parameter);
			}
			
			return command;
		}

		private bool RunDataSetProcedure(IDbCommand command,out DataSet dataSet)
		{
			IDbDataAdapter adapter = new SqlDataAdapter();
			adapter.SelectCommand = command;

			try
			{
				dataSet = new DataSet();
				adapter.Fill(dataSet);
			}
			catch(Exception ex)
			{
				this.Errores.AgregarError( ex.Message, 1, TipoReglaRota.Otro);
				throw ( ex);
				dataSet = null;
				return false;
			}
			finally
			{
				Close();
			}

			return true;
		}

		private bool RunNonQueryProcedure(IDbCommand command,out int recordsAffected)
		{
			try
			{
				recordsAffected = command.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				this.Errores.AgregarError( ex.Message, 1, TipoReglaRota.Otro);
				throw ( ex);
				recordsAffected = 0;
				return false;
			}
			finally
			{
				Close();
			}

			return true;
		}

		private bool RunScalarProcedure(IDbCommand command,out object value)
		{
			try
			{
				value = command.ExecuteScalar();
			}
			catch(Exception ex)
			{
				this.Errores.AgregarError( ex.Message, 1, TipoReglaRota.Otro);
				throw ( ex);
				value = null;
				return false;
			}
			finally
			{
				Close();
			}

			return true;
		}

		public static SqlParameter SqlInputParam(string paramName, DbTipo dbType, int size, object value) 
		{
			return SqlParam(paramName, dbType, size, ParameterDirection.Input, value);
		}

		public static SqlParameter SqlOutputParam(string paramName, DbTipo dbType, int size) 
		{
			return SqlParam(paramName, dbType, size, ParameterDirection.Output, null);
		}

		public static SqlParameter SqlParam(string paramName, DbTipo dbType, int size, ParameterDirection direction, object value) 
		{
			SqlParameter param;
			SqlDbType tipo = SqlDbType.VarChar;

			switch( dbType )
			{
				case DbTipo.Bool:
					tipo = SqlDbType.Bit;
					break;
				case DbTipo.Binario:
					tipo = SqlDbType.Binary;
					break;
				case DbTipo.Cadena:
					tipo = SqlDbType.VarChar;
					break;
				case DbTipo.CadenaFija:
					tipo = SqlDbType.Char;
					break;
				case DbTipo.Entero:
					tipo = SqlDbType.Int;
					break;
				case DbTipo.Fecha:
					tipo = SqlDbType.DateTime;
					break;
				case DbTipo.Moneda:
					tipo = SqlDbType.Money;
					break;
                case DbTipo.Float:
                    tipo = SqlDbType.Float;
                    break;
			}

			if(size > 0)
				param = new SqlParameter(paramName, tipo, size);
			else
				param = new SqlParameter(paramName, tipo);

			param.Direction = direction;

			if (!(direction == ParameterDirection.Output && value == null))
				param.Value = value;

			return param;
		}

		#endregion
	}
}
